package jsyf.oa.dao.report.intentCustomer;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.report.intentCustomer.ReportCustomerEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2018/4/5.
 */
@Repository
public class ReportCustomerDao extends BaseDao {

    public ReportCustomerDao() {
        this.iBaseEntity = new ReportCustomerEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ReportCustomerEntity.class);
    }

    /*个人*/
    public List<JSONObject> getForPersonalIntentCustomerList(String userId, String key, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_wrt_customer.`NAME` AS  name,\n" +
                "t_wrt_customer.`ID` AS  id,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.SOURCE AS source,\n" +
                "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT as customerCredit,\n" +
                "t_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                "t_wrt_customer.CITY_NAME AS cityName,\n" +
                "t_wrt_customer.COUNTY_NAME AS countyName,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "WHERE 1=1" + "\n");
        sql.append(" AND CREATE_USER_ID=" + "'" + userId + "'" + "\n");
        sql.append("AND( t_wrt_customer.`NAME` LIKE " + " '%" + key + "%'" +
                " OR(t_wrt_customer.INTENTION_MODEL LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.BUY_DATE LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.BUY_TYPE LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.OTHER_CONDITION LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.TEL LIKE " + " '%" + key + "%'" + ")  )" + "\n");
        sql.append("AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("GROUP BY\n" +
                "t_wrt_customer.`NAME`,\n" +
                "t_wrt_customer.TEL,\n" +
                "t_wrt_customer.SOURCE ,\n" +
                "t_wrt_customer.INTENTION_MODEL ,\n" +
                "t_wrt_customer.BUY_DATE ,\n" +
                "t_wrt_customer.BUY_TYPE ,\n" +
                "t_wrt_customer.EXISTING_DEVICE,\n" +
                "t_wrt_customer.FUND_CONDITION ,\n" +
                "t_wrt_customer.OTHER_CONDITION" + "\n");
        sql.append("ORDER BY t_wrt_customer.CREATE_TIME DESC ");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> records = super.queryForJsonList(sql.toString());
        return records;
    }

    /*查询个人数量*/
    public Integer getCountPersonalIntentCustomerList(String userId, String key) {
        StringBuffer sql = new StringBuffer();
        int recordCount = 0;
        sql.append(
                "SELECT COUNT(1) AS count\n" +
                        "FROM (" +
                        "SELECT\n" +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                        "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation\n" +
                        "FROM\n" +
                        "t_wrt_customer\n" +
                        "WHERE 1=1" + "\n");
        sql.append(" AND CREATE_USER_ID=" + "'" + userId + "'" + "\n");
        sql.append("AND( t_wrt_customer.`NAME` LIKE " + " '%" + key + "%'" +
                " OR(t_wrt_customer.INTENTION_MODEL LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.BUY_DATE LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.BUY_TYPE LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.OTHER_CONDITION LIKE " + " '%" + key + "%'" + ") " +
                " OR(t_wrt_customer.TEL LIKE " + " '%" + key + "%'" + ")  )" + "\n");
        sql.append("AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("GROUP BY\n" +
                "t_wrt_customer.`NAME`,\n" +
                "t_wrt_customer.TEL,\n" +
                "t_wrt_customer.SOURCE ,\n" +
                "t_wrt_customer.INTENTION_MODEL ,\n" +
                "t_wrt_customer.BUY_DATE ,\n" +
                "t_wrt_customer.BUY_TYPE ,\n" +
                "t_wrt_customer.EXISTING_DEVICE,\n" +
                "t_wrt_customer.FUND_CONDITION ,\n" +
                "t_wrt_customer.OTHER_CONDITION\n" +
                ") AS sys"
        );
        String count = super.queryForString(sql.toString());
        recordCount = Integer.parseInt(count);
        return recordCount;
    }

    /*部门*/
    public List<JSONObject> getForDepartIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysCustomer.regionId,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num,\n" +
                "COUNT(case when (sysCustomer.buyDate='一周内'  OR sysCustomer.buyDate='一周' )then sysCustomer.buyDate end ) AS weekNum,\n" +
                "COUNT(case when (sysCustomer.buyDate='一月内' OR sysCustomer.buyDate='本月') then sysCustomer.buyDate end ) AS MonNum,\n" +
                "COUNT(case when sysCustomer.buyDate='一月以上' then sysCustomer.buyDate end ) AS MoreNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo,\n" +
                "t_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("AND (\n" +
                "  t_sys_user.ID =" + " '" + userId + "'" + "\n");
        sql.append("OR t_sys_user.PARENT_ID =" + " '" + userId + "'" + "\n");
        sql.append("OR (\n" +
                "\t\t\t\t\tt_sys_user.ID in(\n" +
                "\t\t\t\t\t\t\tSELECT USER_ID FROM t_sys_user_2_region \n" +
                "\t\t\t\t\t\t\tWHERE \n" +
                "\t\t\t\t\t\t\tREGION_ID in(\n" +
                "\t\t\tSELECT REGION_ID FROM t_sys_user_2_region WHERE USER_ID =" + " '" + userId + "'" + "\n");
        sql.append("\t)\n" +
                "\t\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\tAND t_sys_role_name.FRONT_FLAG = 1\n" +
                "\t\t\t\t)\n" +
                ")\n" +
                "GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName\n" +
                "ORDER BY sysCustomer.departSortNo,sysCustomer.roleNameSortNo");
        return super.queryForJsonList(sql.toString());
    }

    /*公司*/
    public List<JSONObject> getForCompanyIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysCustomer.regionId ,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num,\n" +
                "COUNT(case when (sysCustomer.buyDate='一周内'  OR sysCustomer.buyDate='一周' )then sysCustomer.buyDate end ) AS weekNum,\n" +
                "COUNT(case when (sysCustomer.buyDate='一月内' OR sysCustomer.buyDate='本月') then sysCustomer.buyDate end ) AS MonNum,\n" +
                "COUNT(case when sysCustomer.buyDate='一月以上' then sysCustomer.buyDate end ) AS MoreNum\n" +
                "\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo,\n" +
                "t_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND t_sys_depart.COMPANY_ID= (\n" +
                "\n" +
                "SELECT t_sys_depart.COMPANY_ID FROM t_sys_depart WHERE ID in(\n" +
                "\t\t\tSELECT t_sys_role.DEPART_ID from t_sys_role WHERE ID in(" + "\n");
        sql.append("SELECT t_sys_user.ROLE_ID from t_sys_user WHERE ID =" + "'" + userId + "')))" + "\n");
        sql.append("GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName\n" +
                "ORDER BY\n" +
                "sysCustomer.departSortNo,sysCustomer.roleNameSortNo");
        return super.queryForJsonList(sql.toString());
    }

    /*集团*/
    public List<JSONObject> getForGroupIntentCustomerList(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "company.companyId AS companyId,\n" +
                "company.companyName AS companyName,\n" +
                "IFNULL(newCustomer.newCount,0) as num,\n" +
                "IFNULL(newCustomer.weekNum,0) as weekNum,\n" +
                "IFNULL(newCustomer.MonNum,0) as MonNum,\n" +
                "IFNULL(newCustomer.MoreNum,0) as MoreNum\n" +
                "FROM\n" +
                "(\n" +
                "SELECT \n" +
                "\tID AS companyId,\n" +
                "`NAME` AS companyName,\n" +
                "SORT_NO AS sortNo\n" +
                "FROM \n" +
                "t_sys_company\n" +
                "WHERE  ID in(" + companyId + ")) AS company" + "\n");
        sql.append("LEFT JOIN\n" +
                "(\n" +
                "SELECT \n" +
                "t_sys_depart.COMPANY_ID AS companyId,\n" +
                "COUNT(1) AS newCount,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一周内'  OR t_wrt_customer.BUY_DATE='一周' )then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when (t_wrt_customer.BUY_DATE='一月内' OR t_wrt_customer.BUY_DATE='本月') then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND\t\tt_sys_depart.COMPANY_ID  in(" + companyId + ")" + "\n");
        sql.append("GROUP BY\n" +
                "t_sys_depart.COMPANY_ID\n" +
                "\n" +
                ") AS newCustomer ON newCustomer.companyId=company.companyId\n" +
                "\n" +
                "ORDER BY company.sortNo");
        return super.queryForJsonList(sql.toString());
    }

    /*集团下部门*/
    public List<JSONObject> getForDepartByCompanyId(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "sysCustomer.regionId,\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName,\n" +
                "COUNT(sysCustomer.customerId) AS num,\n" +
                "COUNT(case when (sysCustomer.buyDate='一周内'  OR sysCustomer.buyDate='一周' )then sysCustomer.buyDate end ) AS weekNum,\n" +
                "COUNT(case when (sysCustomer.buyDate='一月内' OR sysCustomer.buyDate='本月') then sysCustomer.buyDate end ) AS MonNum,\n" +
                "COUNT(case when sysCustomer.buyDate='一月以上' then sysCustomer.buyDate end ) AS MoreNum\n" +
                "\n" +
                "FROM\n" +
                "(\n" +
                "SELECT  \n" +
                "(\n" +
                "\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`NAME`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionName`,\n" +
                "(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN (\n" +
                "\t\t\t(\n" +
                "\t\t\t\t`t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "\t\t\t)\n" +
                "\t\t\tAND (\n" +
                "\t\t\t\t`t_sys_role_name`.`AREA_TYPE` = 0\n" +
                "\t\t\t)\n" +
                "\t\t) THEN\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` <> 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tELSE\n" +
                "\t\t\t(\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\t`t_sys_region`.`ID`\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\t`t_sys_region`\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t`t_sys_region`.`PARENT_ID` = 0\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\tAND `t_sys_region`.`ID` IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`REGION_ID`\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t`t_sys_user_2_region`\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t\t\t\t`t_sys_user_2_region`.`USER_ID` = `t_sys_user`.`ID`\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t)\n" +
                "\t\t\t\tLIMIT 0,\n" +
                "\t\t\t\t1\n" +
                "\t\t\t)\n" +
                "\t\tEND\n" +
                "\t) AS `regionId`,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS roleName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_sys_depart.SORT_NO AS departSortNo,\n" +
                "t_sys_role_name.SORT_NO AS roleNameSortNo,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_sys_role_name.`NAME`='区域经理'\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND t_sys_depart.COMPANY_ID=" + "'" + companyId + "'" + "\n");
        sql.append("GROUP BY\n" +
                "regionName ,\n" +
                "t_sys_depart.`NAME` ,\n" +
                "t_sys_role_name.`NAME` ,\n" +
                "t_sys_user.ID ,\n" +
                "t_sys_user.`NAME` ,\n" +
                "t_wrt_customer.ID\n" +
                ") AS  sysCustomer\n" +
                "GROUP BY\n" +
                "sysCustomer.regionName ,\n" +
                "sysCustomer.roleName,\n" +
                "sysCustomer.userId,\n" +
                "sysCustomer.userName,\n" +
                "sysCustomer.departName\n" +
                "ORDER BY sysCustomer.departSortNo,sysCustomer.roleNameSortNo");
        return super.queryForJsonList(sql.toString());
    }

    /*获取身份标志manageFlag,frontFlag,areaType,*/
    public JSONObject getManageFlagAndFrontFlagAndAreaTypeByUserId(String userId) {
        String sql = "SELECT \n" +
                "t_sys_user.ID as userId,\n" +
                "t_sys_user.`NAME` as userName,\n" +
                "t_sys_role_name.FRONT_FLAG as frontFlag,\n" +
                "t_sys_role_name.AREA_TYPE as areaType,\n" +
                "t_sys_role_name.MANAGE_FLAG as manageFlag\n" +
                " from t_sys_user \n" +
                "LEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "WHERE t_sys_user.`ID` = '" + userId + "'";
        JSONObject objUser = super.queryForJsonObject(sql, null);
        return objUser;
    }

    /*获取所有客户*/
    public List<JSONObject> getAllIntentCustomerList(String userId, String key, String companyId, int requestPage, int pageSize, JSONObject roleFlag) {
         /*查询所有客户*/
        String sqlCustomer = "";
        if (roleFlag.getString("manageFlag").equals("1")) {
            //集团管理员
            sqlCustomer = "SELECT\n" +
                    "t_wrt_customer.`NAME` AS  name,\n" +
                    "t_wrt_customer.`ID` AS  id,\n" +
                    "t_wrt_customer.TEL AS tel,\n" +
                    "t_wrt_customer.SOURCE AS source,\n" +
                    "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                    "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                    "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                    "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                    "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                    "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                    "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                    "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                    "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%')" + "\n" +
                    "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT \n" +
                    "t_sys_user.ID AS id\n" +
                    "FROM \n" +
                    "t_sys_user\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                    "WHERE 1=1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_sys_role_name.`NAME`='区域经理'\n" +
                    "AND t_sys_user.JOB_STATE<>1\n" +
                    "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                    "GROUP BY \n" +
                    "t_wrt_customer.ID\n" +
                    "order by \n" +
                    "t_wrt_customer.CREATE_TIME";

        } else if (roleFlag.getString("frontFlag").equals("1")) {
            if (roleFlag.getString("areaType").equals("1")) {
                //大区经理
                sqlCustomer = "SELECT\n" +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "WHERE 1=1\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                        "\n" +
                        "SELECT \n" +
                        "id \n" +
                        "FROM v_sys_user\n" +
                        "WHERE 1=1" + "\n" +
                        "AND( (parentId=" + "'" + userId + "')" + "\n" +
                        "OR(id=" + "'" + userId + "'" + "))" + "\n" +
                        "AND v_sys_user.jobState<>1" + "\n" +
                        "AND v_sys_user.roleName='区域经理'"+
                        "GROUP BY \n" +
                        "id\n" +
                        "\n" +
                        ")\n" +
                        "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + "OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                        "GROUP BY \n" +
                        "t_wrt_customer.`NAME`,\n" +
                        "t_wrt_customer.TEL,\n" +
                        "t_wrt_customer.SOURCE ,\n" +
                        "t_wrt_customer.INTENTION_MODEL ,\n" +
                        "t_wrt_customer.BUY_DATE ,\n" +
                        "t_wrt_customer.BUY_TYPE ,\n" +
                        "t_wrt_customer.EXISTING_DEVICE,\n" +
                        "t_wrt_customer.FUND_CONDITION ,\n" +
                        "t_wrt_customer.OTHER_CONDITION \n"
                        + " order by t_wrt_customer.CREATE_TIME ";
            } else {
                //区域经理
                sqlCustomer = "select " +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                        " from t_wrt_customer where 1=1" + "\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND CREATE_USER_ID=" + "'" + userId + "'" + "\n" +
                        "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")" + "\n"
                        + "GROUP BY \n" +
                        "t_wrt_customer.`NAME`,\n" +
                        "t_wrt_customer.TEL,\n" +
                        "t_wrt_customer.SOURCE ,\n" +
                        "t_wrt_customer.INTENTION_MODEL ,\n" +
                        "t_wrt_customer.BUY_DATE ,\n" +
                        "t_wrt_customer.BUY_TYPE ,\n" +
                        "t_wrt_customer.EXISTING_DEVICE,\n" +
                        "t_wrt_customer.FUND_CONDITION ,\n" +
                        "t_wrt_customer.OTHER_CONDITION " + "\n"
                        + " order by t_wrt_customer.CREATE_TIME " + "\n";
            }
        } else {
            //其他岗位
            sqlCustomer = " SELECT\n" +
                    "t_wrt_customer.`NAME` AS  name,\n" +
                    "t_wrt_customer.`ID` AS  id,\n" +
                    "t_wrt_customer.TEL AS tel,\n" +
                    "t_wrt_customer.SOURCE AS source,\n" +
                    "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                    "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                    "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                    "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                    "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                    "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                    "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                    "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                    "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                    "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                    "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT\n" +
                    "t_sys_user.ID\n" +
                    "FROM\n" +
                    "t_sys_user\n" +
                    "LEFT JOIN  t_sys_user_2_region  ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                    "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                    "WHERE 1=1\n" +
                    "AND t_sys_user_2_region.REGION_ID  in(\n" +
                    "SELECT REGION_ID FROM t_sys_user_2_region \n" +
                    "WHERE USER_ID=" + "'" + userId + "')" + "\n" +
                    "AND  t_sys_user.JOB_STATE<>1 \n" +
                    "GROUP BY \n" +
                    "t_sys_user.ID\n" +
                    "\n" + ")" +
                    "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")" + "\n" +
                    "GROUP BY \n" +
                    "t_wrt_customer.ID\n" +
                    " order by t_wrt_customer.CREATE_TIME";
        }
        StringBuffer sqlBuffer = new StringBuffer(sqlCustomer);
        super.appendPageSql(sqlBuffer, Integer.valueOf(requestPage), Integer.valueOf(pageSize));
        List<JSONObject> records = super.queryForJsonList(sqlBuffer.toString());
        return records;
    }

    /*获取查询数目*/
    public Integer getCountByCompanyIdAndUserIdAndKey(String companyId, String userId, String key, JSONObject roleFlag) {
         /*获得查询数目*/
        int recordCount = 0;
        String sqlCustomer = "";
        if (roleFlag.getString("manageFlag").equals("1")) {
            String all = "SELECT\n" +
                    "COUNT(1) AS count\n" +
                    "FROM\n" +
                    "\tt_wrt_customer\n" +
                    "WHERE 1=1\n" +
                    "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                    "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                    "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                    "AND t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                    "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                    "SELECT \n" +
                    "t_sys_user.ID AS id\n" +
                    "FROM \n" +
                    "t_sys_user\n" +
                    "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                    "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                    "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                    "WHERE 1=1\n" +
                    "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                    "AND t_sys_role_name.`NAME`='区域经理'\n" +
                    "AND t_sys_user.JOB_STATE<>1\n" +
                    "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                    "AND(t_wrt_customer.`NAME` LIKE  " + " '%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE  " + " '%" + key + "%'" + ")";
            JSONObject objCustomer = super.queryForJsonObject(all, null);
            recordCount = objCustomer.getInteger("count");
        } else {
            if (roleFlag.getString("manageFlag").equals("1")) {
                //集团管理员
                sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                        "from (\n" +
                        "SELECT\n" +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "WHERE 1=1\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%')" + "\n" +
                        "AND  t_wrt_customer.CREATE_USER_ID in(\n" +
                        "SELECT \n" +
                        "t_sys_user.ID AS id\n" +
                        "FROM \n" +
                        "t_sys_user\n" +
                        "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                        "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                        "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                        "WHERE 1=1\n" +
                        "AND t_sys_role_name.`NAME`='区域经理'\n" +
                        "t_sys_user.JOB_STATE<>1\n"+
                        "AND t_sys_depart.COMPANY_ID in(" + companyId + "))" + "\n" +
                        "GROUP BY \n" +
                        "t_wrt_customer.ID\n" +
                        "order by \n" +
                        "t_wrt_customer.CREATE_TIME\n" + ") AS sys";

            } else if (roleFlag.getString("frontFlag").equals("1")) {
                if (roleFlag.getString("areaType").equals("1")) {
                    //大区经理
                    sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                            "from (\n" +
                            "SELECT\n" +
                            "t_wrt_customer.`NAME` AS  name,\n" +
                            "t_wrt_customer.`ID` AS  id,\n" +
                            "t_wrt_customer.TEL AS tel,\n" +
                            "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                            "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                            "t_wrt_customer.SOURCE AS source,\n" +
                            "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                            "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                            "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                            "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                            "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                            "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                            "FROM\n" +
                            "\tt_wrt_customer\n" +
                            "WHERE 1=1\n" +
                            "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                            "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                            "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                            "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                            "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                            "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                            "\n" +
                            "SELECT \n" +
                            "id \n" +
                            "FROM v_sys_user\n" +
                            "WHERE 1=1" + "\n" +
                            "AND( (parentId=" + "'" + userId + "')" + "\n" +
                            "OR(id=" + "'" + userId + "'" + "))" + "\n" +
                            "AND v_sys_user.jobState<>1" + "\n" +
                            "GROUP BY \n" +
                            "id\n" +
                            "\n" +
                            ")\n" +
                            "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + "OR  t_wrt_customer.TEL LIKE " + "'%" + key + "%'" + ")" + "\n" +
                            "GROUP BY \n" +
                            "t_wrt_customer.`NAME`,\n" +
                            "t_wrt_customer.TEL,\n" +
                            "t_wrt_customer.SOURCE ,\n" +
                            "t_wrt_customer.INTENTION_MODEL ,\n" +
                            "t_wrt_customer.BUY_DATE ,\n" +
                            "t_wrt_customer.BUY_TYPE ,\n" +
                            "t_wrt_customer.EXISTING_DEVICE,\n" +
                            "t_wrt_customer.FUND_CONDITION ,\n" +
                            "t_wrt_customer.OTHER_CONDITION \n"
                            + " order by t_wrt_customer.CREATE_TIME\n " + ") AS sys";
                } else {
                    //区域经理
                    sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                            "from (\n" +
                            "select " +
                            "t_wrt_customer.`NAME` AS  name,\n" +
                            "t_wrt_customer.`ID` AS  id,\n" +
                            "t_wrt_customer.TEL AS tel,\n" +
                            "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                            "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                            "t_wrt_customer.SOURCE AS source,\n" +
                            "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                            "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                            "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                            "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                            "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                            "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                            " from t_wrt_customer where 1=1" + "\n" +
                            "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                            "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                            "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                            "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                            "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                            "AND CREATE_USER_ID=" + "'" + userId + "'" + "\n" +
                            "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")" + "\n"
                            + "GROUP BY \n" +
                            "t_wrt_customer.`NAME`,\n" +
                            "t_wrt_customer.TEL,\n" +
                            "t_wrt_customer.SOURCE ,\n" +
                            "t_wrt_customer.INTENTION_MODEL ,\n" +
                            "t_wrt_customer.BUY_DATE ,\n" +
                            "t_wrt_customer.BUY_TYPE ,\n" +
                            "t_wrt_customer.EXISTING_DEVICE,\n" +
                            "t_wrt_customer.FUND_CONDITION ,\n" +
                            "t_wrt_customer.OTHER_CONDITION " + "\n"
                            + " order by t_wrt_customer.CREATE_TIME\n " + ") AS sys";
                }
            } else {
                //其他岗位
                sqlCustomer = "SELECT COUNT(1) AS  count\n" +
                        "from (\n" +
                        " SELECT\n" +
                        "t_wrt_customer.`NAME` AS  name,\n" +
                        "t_wrt_customer.`ID` AS  id,\n" +
                        "t_wrt_customer.TEL AS tel,\n" +
                        "t_wrt_customer.SOURCE AS source,\n" +
                        "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                        "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                        "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                        "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                        "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                        "t_wrt_customer.EXISTING_DEVICE AS existingDevice,\n" +
                        "t_wrt_customer.FUND_CONDITION AS fundCondition,\n" +
                        "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                        "FROM\n" +
                        "\tt_wrt_customer\n" +
                        "WHERE 1=1\n" +
                        "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                        "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND t_wrt_customer.CREATE_USER_ID in(\n" +
                        "SELECT\n" +
                        "t_sys_user.ID\n" +
                        "FROM\n" +
                        "t_sys_user\n" +
                        "LEFT JOIN  t_sys_user_2_region  ON t_sys_user_2_region.USER_ID=t_sys_user.ID\n" +
                        "LEFT JOIN t_sys_user_leave ON t_sys_user_leave.USER_ID=t_sys_user.ID\n" +
                        "WHERE 1=1\n" +
                        "AND t_sys_user_2_region.REGION_ID  in(\n" +
                        "SELECT REGION_ID FROM t_sys_user_2_region \n" +
                        "WHERE USER_ID=" + "'" + userId + "')" + "\n" +
                        "AND  t_sys_user.JOB_STATE<>1 \n" +
                        "GROUP BY \n" +
                        "t_sys_user.ID\n" +
                        "\n" + ")" +
                        "AND(t_wrt_customer.`NAME` LIKE" + "'%" + key + "%'" + " OR  t_wrt_customer.TEL LIKE" + "'%" + key + "%'" + ")" + "\n" +
                        "GROUP BY \n" +
                        "t_wrt_customer.ID\n" +
                        " order by t_wrt_customer.CREATE_TIME\n" + ") AS sys";
            }
            String count = super.queryForString(sqlCustomer.toString());
            recordCount = Integer.parseInt(count);
        }
        return recordCount;
    }

    /*获取APP大区经理意向客户*/
    public List<JSONObject> getForAppBigRegionIntentCustomerList(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_sys_user.ID AS userId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.CUSTOMER_TYPE as customerType,\n" +
                "t_wrt_customer.CUSTOMER_LEVEL as customerLevel,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT as customerCredit,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                " MAX(t_wrt_customer_visit.CREATE_TIME) AS visitTime\n" +
                "FROM\n" +
                " t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_wrt_customer_visit ON t_wrt_customer_visit.CUSTOMER_ID=t_wrt_customer.ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n " +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n");
        sql.append("AND (\n" +
                "  t_sys_user.ID =" + " '" + userId + "'\n");
        sql.append("\tOR t_sys_user.PARENT_ID =" + " '" + userId + "')\n");
        sql.append("AND t_sys_user.JOB_STATE<>1\n");
        sql.append("GROUP BY\n" +
                "t_sys_user.SORT_NO,t_sys_user.ID,t_wrt_customer.ID\n");
        return super.queryForJsonList(sql.toString());
    }

    /*意向列表*/
    public List<JSONObject> getForAppIntentCustomerListsByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_user.`NAME` AS userName,\n" +
                "t_sys_user.`ID` AS userId,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "t_wrt_customer.PROVINCE_NAME AS provinceName,\n" +
                "t_wrt_customer.CITY_NAME AS cityName,\n" +
                "t_wrt_customer.COUNTY_NAME AS countyName,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.INTENTION_MODEL  AS intentionModel,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n"
        );
        sql.append("AND (\n" +
                "  t_sys_user.ID =" + " '" + userId + "'\n");
        sql.append("\tOR t_sys_user.PARENT_ID =" + " '" + userId + "')\n");
        sql.append("AND t_sys_user.JOB_STATE<>1\n" +
                "GROUP BY\n" +
                "t_wrt_customer.ID" + "\n");
        sql.append("ORDER BY t_wrt_customer.CREATE_TIME desc");
        return super.queryForJsonList(sql.toString());
    }

    /*获取数量*/
    public Integer getCountForAppIntentCustomerListsByUserId(String userId) {
        int recordCount = 0;
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "COUNT(1) AS count\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "WHERE 1=1\n" +
                "AND (t_wrt_customer.THROW_FLAG<>1 OR t_wrt_customer.THROW_FLAG IS NULL)\n"+
                "AND t_wrt_customer.INTENTION_MODEL IS NOT NULL\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL" + "\n"
        );
        sql.append("AND t_sys_user.ID=" + " '" + userId + "'" + "\n");
        sql.append("AND t_sys_user.JOB_STATE<>1\n");
        String count = super.queryForString(sql.toString());
        recordCount = Integer.parseInt(count);
        return recordCount;
    }

    public List<JSONObject> getForUserInfo(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "`name` AS name,\n" +
                "id AS userId\n" +
                "FROM\n" +
                "v_sys_user\n" +
                " WHERE 1=1" + "\n");
        sql.append("AND (id=" + " '" + userId + "'" + "\n");
        sql.append("OR (parentId=" + " '" + userId + " '))" + "\n");
        sql.append("AND jobState<>1\n");
        sql.append("ORDER BY roleSortNo");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getForProvinceById(String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "`NAME` AS name,\n" +
                "ID AS id\n" +
                "FROM\n" +
                "t_sys_province\n" +
                "WHERE PARENT_ID=" + " '" + id + "'");
        return super.queryForJsonList(sql.toString());
    }

    /*查询是否有重复电话*/
    public String getForTel(String tel) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "TEL AS tel\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "WHERE TEL=" + " '" + tel + "'");
        String t = super.queryForString(sql.toString());
        return t;
    }
}